/**
 * 
 */
package com.ctg.payroll2.util.xlsImport;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.log4j.Logger;
import org.jdom.Attribute;
import org.jdom.Document;
import org.jdom.Element;
import jxl.read.biff.BiffException;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.DateFormulaCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.NumberFormulaCell;
import jxl.Sheet;
import jxl.Workbook;

/**
 * @author
 * 
 */
public class JxlUtils {
	private static final Logger logger = Logger.getLogger(JxlUtils.class);
	public static final String GOODRECORES = "Good.Records";
	public static final String BADRECORES = "Bad.Records";

	// 26进制
	private static final int DEFAULT_ALPHABET_LENGTH = 26;
	// 默认工作表索引
	private static final int DEFAULT_SHEET_INDEX = 0;
	// 默认的根元素名
	private static final String DEFAULT_DOCUMENT_ROOT = "Mappings";
	// 默认的元素名
	private static final String DEFAULT_ELEMENT = "Mapping";
	//

	// 文件路径
	private String path;
	// 工作簿名
	private String sheetName;
	// 是否包含表头
	private boolean hasHead = false;
	// 表头所在行数
	private int headIndexRow = -1;
	// 起始行
	private int startIndexRow = -1;
	// 终止行
	private int endIndexRow = -1;
	// 对应关系
	private Map<String, String> mapping;
	// 配置文件
	private String configuration;
	// 配置Document
	private Document document;

	// 工作表
	private Workbook workbook;
	// 工作簿
	private Sheet sheet;
	// 表头列对应关系
	private HeadMapping[] headMapping;

	/**
	 * 初始化工作表和工作簿
	 * 
	 * @throws IOException
	 * @throws BiffException
	 */
	private void init() throws IOException, BiffException {
		workbook = Workbook.getWorkbook(new File(path));
		if (null == sheetName) {
			sheet = workbook.getSheet(DEFAULT_SHEET_INDEX);
		} else {
			sheet = workbook.getSheet(sheetName);
		}
		startIndexRow = startIndexRow < 0 ? 0 : startIndexRow;
		endIndexRow = endIndexRow < 0 ? sheet.getRows() - 1 : endIndexRow;
		logger.debug("Filepath:" + path + ";Sheet:" + sheet.getName()
				+ ";StartRow:" + startIndexRow + ";EndRow:" + endIndexRow);
	}

	/**
	 * 根据实体bean的属性产生表头列对应关系
	 * 
	 * @param <T>
	 * @param entityClass
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 */
	private <T> void mapping(Class<T> entityClass) throws SecurityException,
			NoSuchFieldException {
		headMapping = new HeadMapping[mapping.entrySet().size()];
		Iterator<Map.Entry<String, String>> items = mapping.entrySet()
				.iterator();

		if (hasHead) {
			// @ TODO 具有表头的excel的实现
		} else {
			int i = 0;
			while (items.hasNext()) {
				Map.Entry<String, String> entry = items.next();
				HeadMapping hm = new HeadMapping();
				hm.setColumn(entry.getKey());
				hm.setProperty(entry.getValue());
				hm.setIndex(getColumnIndex(entry.getKey()));
				if (entityClass != null) {
					hm.setType(entityClass.getDeclaredField(hm.getProperty()).getType().getName());
				}
				headMapping[i++] = hm;
			}
		}
		if (logger.isDebugEnabled()) {
			StringBuffer sb = new StringBuffer();
			sb.append("HeadMapping:[");
			for (int i = 0; i < headMapping.length; i++) {
				sb.append("{" + headMapping[i] + "};");
			}
			sb.setLength(sb.length() - 1);
			sb.append("]");
			logger.debug(sb.toString());
		}
	}

	/**
	 * 关闭工作表
	 */
	private void close() {
		workbook.close();
	}

	/**
	 * 读取表，并转换成entityClass类的集合
	 * 
	 * @param <T>
	 * @param entityClass
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 * @throws SecurityException
	 * @throws NoSuchFieldException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public <T> Map<String, List> transform(Class<T> entityClass)
			throws BiffException, IOException, SecurityException,
			NoSuchFieldException, InstantiationException,
			IllegalAccessException {
		T t = null;
		HeadMapping hm = null;

		init();
		mapping(entityClass);
		List<T> list = new ArrayList<T>(endIndexRow - startIndexRow + 1);
		List<Map<String, String>> badList = new ArrayList<Map<String, String>>();
		boolean flag = true;

		for (int i = startIndexRow; i <= endIndexRow; i++) {
			t = entityClass.newInstance();
			flag = true;
			for (int j = 0; j < headMapping.length; j++) {
				hm = headMapping[j];
				Cell cell = sheet.getCell(hm.getIndex(), i);

				try {
					BeanUtils.copyProperty(t, hm.getProperty(), CellUtils
							.getCellValue(hm, cell));
				} catch (NumberFormatException nfe) {
					flag = false;
					if (logger.isDebugEnabled()) {
						logger.debug(hm.getProperty()
								+ "convert a NumberFormatException!");
					}
					break;
				} catch (InvocationTargetException e) {
					flag = false;
					if (logger.isDebugEnabled()) {
						logger.debug(hm.getProperty()
								+ "convert a InvocationTargetException!");
					}
					break;
				}
			}
			if (flag) {
				list.add(t);
			} else {
				Map<String, String> map = new HashMap<String, String>();
				for (int j = 0; j < headMapping.length; j++) {
					hm = headMapping[j];
					map.put(hm.getProperty(), sheet.getCell(hm.getIndex(), i)
							.getContents());
				}
				badList.add(map);
			}
		}

		close();

		Map<String, List> map = new HashMap<String, List>();
		map.put(GOODRECORES, list);
		map.put(BADRECORES, badList);

		return map;
	}

	public Map<String, List<Map<String, String>>> transform()
			throws BiffException, IOException, SecurityException,
			NoSuchFieldException, InstantiationException,
			IllegalAccessException {

		HeadMapping hm = null;

		init();
		mapping(null);
		List<Map<String, String>> list = new ArrayList<Map<String, String>>(
				endIndexRow - startIndexRow + 1);
		List<Map<String, String>> badList = new ArrayList<Map<String, String>>(
				0);
		boolean flag = true;

		for (int i = startIndexRow; i <= endIndexRow; i++) {
			Map<String, String> map = new HashMap<String, String>();
			for (int j = 0; j < headMapping.length; j++) {
				hm = headMapping[j];
				Cell cell = sheet.getCell(hm.getIndex(), i);
				map.put(hm.getProperty(), cell.getContents());
			}
			list.add(map);
		}

		close();

		Map<String, List<Map<String, String>>> _map = new HashMap<String, List<Map<String, String>>>();
		_map.put(GOODRECORES, list);
		_map.put(BADRECORES, badList);

		return _map;
	}

	public void setMapping(Map<String, String> mapping) {
		this.mapping = mapping;
	}

	public String getPath() {
		return path;
	}

	public void setPath(String path) {
		this.path = path;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public int getStartIndexRow() {
		return startIndexRow;
	}

	public void setStartIndexRow(int startIndexRow) {
		this.startIndexRow = startIndexRow;
	}

	public int getEndIndexRow() {
		return endIndexRow;
	}

	public void setEndIndexRow(int endIndexRow) {
		this.endIndexRow = endIndexRow;
	}

	public Map<String, String> getMapping() {
		return mapping;
	}

	public HeadMapping[] getHeadMapping() {
		return this.headMapping;
	}

	/**
	 * 根据索引获取默认表标示
	 * 
	 * @param number
	 * @return
	 */
	public String getHeader(int number) {
		StringBuffer sb = new StringBuffer();
		if (number / DEFAULT_ALPHABET_LENGTH > 0) {
			sb.append((char) (number / DEFAULT_ALPHABET_LENGTH + 64));
		}
		sb.append((char) (number % DEFAULT_ALPHABET_LENGTH + 65));
		return sb.toString();
	}

	/**
	 * 根据标示获取列索引
	 * 
	 * @param index
	 * @return
	 */
	public int getColumnIndex(String index) {
		int row = 0;
		int length = index.length();
		for (int i = 0; i < length; i++) {
			int c = ((int) index.charAt(i)) - 65;
			if (length - 1 - i == 0)
				row += c;
			else
				row += (index.length() - 1 - i) * DEFAULT_ALPHABET_LENGTH
						* (c + 1);
		}
		return row;
	}
}
